package com.cn.zaprk;

import com.cn.zaprk.beans.User;
import com.cn.zaprk.utils.DruidUtils;
import com.cn.zaprk.utils.PageInfo;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @PackageName: com.cn.zaprk
 * @ClassName: FindAllUserServlet
 * @Description:
 * @author: RZS
 * @date: 2022/4/22  11:07
 */
@WebServlet("/find_all.do")
public class FindAllUserServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 准备连接信息
        Connection conn = null;
        PreparedStatement stat = null;// 查询分页
        PreparedStatement stat1 = null;// 查询总数
        ResultSet res = null;
        PageInfo<User> pageInfo = new PageInfo<>();
        List<User> list = new ArrayList<>();

        try {
            // 获取数据库连接
            conn = DruidUtils.getConn();
            // 编写sql
            String sql = "select id, username, password, phone, email, salary from user limit 0, 10";
            String sql1 = "select count(*) num from user";
            // 创建预编译sql
            stat = conn.prepareStatement(sql);
            stat1 = conn.prepareStatement(sql1);
            // 替换占位符
            // 执行sql并且获取返回结果集
            res = stat.executeQuery();
            ResultSet resultSet = stat1.executeQuery();
            int number = 0;
            while (resultSet.next()) {
                number = resultSet.getInt(1);
            }

            // 循环取出分页
            while (res.next()) {
                User user = new User();
                user.setId(res.getInt("id"));
                user.setUsername(res.getString("username"));
                user.setPassword(res.getString("password"));
                user.setPhone(res.getString("phone"));
                user.setEmail(res.getString("email"));
                user.setSalary(res.getDouble("salary"));

                // 添加到集合
                list.add(user);
            }

            // 将数据封装到分页信息中
            pageInfo.setSum(number);
            pageInfo.setInfo(list);
            pageInfo.setCurrentPage(0);
            // 绑定数据到请求域中
            request.setAttribute("pageInfo", pageInfo);
            // 转发给JSP处理
            request.getRequestDispatcher("jsp/demo05.jsp").forward(request, response);
        } catch(Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            DruidUtils.close(res, stat, conn);
        }



    }
}
